﻿using ConsoleApplication2.Models;
using System;
using System.Web.Mvc;

namespace ConsoleApplication2.Controllers
{
    public class PayingCustomerController : Controller
    {
        private HMS909Context db = new HMS909Context();
        //
        // GET: /PayingCustomers/


        public ActionResult Index(String toDate, String fromDate)
        {

             if ((string.IsNullOrEmpty(toDate) && (string.IsNullOrEmpty(fromDate))))
            {


                DateTime todayDate = DateTime.Today;
                DateTime startDate = DateTime.Today;
                toDate = todayDate.ToString("d");
                fromDate = startDate.ToString("d");
             }


            ViewData["fromDate"] = fromDate;
            ViewData["toDate"] = toDate;
            string query =  " SELECT arabic_name,convert(varchar,  max([date]), 103) as lastActive,   patient_id as custid , PATIENT as full_name, sum(total_amount_base_2) as total  , sum(patient_amount_base_2) as discounted , [case_#] as patID, mobile"
 + " FROM [HMS909].[dbo].[SO_BillHeader]"
 + " join  [HMS909].[dbo].[cust] "
 + " on  RTRIM(LTRIM([HMS909].[dbo].[cust].[caseNB])) =  RTRIM(LTRIM([CASE_#]))"
 + " where       [DATE] <= '"+toDate+"' and [date] >= '" + fromDate +"'"
 + " group by PATIENT, patient_id , arabic_name, [case_#], mobile"
 +"  order by discounted DESC";

            var list = db.Database.SqlQuery<PayingCustomer>(query);



            return View(list);
        }

        public ActionResult Details(String custid, String toDate, String fromDate)
        {
            ViewData["custid"] = custid;
            String query = "select   HMS909.dbo.SO_BillDETAILsums.[DESCRIPTION], AMOUNT, PATIENT_AMOUNT, ENTRY_DATE  from HMS909.dbo.SO_BillDETAILsums, HMS909.dbo.PSPayment  ,HMS909.dbo.Oe where HMS909.dbo.PSPayment.OEID =  HMS909.dbo.Oe.NUMBER  and HMS909.dbo.Oe.Bill# = HMS909.dbo.SO_BillDETAILsums.Bill# and PSPayment.CUSTID ="+custid;

            query = "SELECT convert(varchar,[date],103) as ENTRY_DATE,  [SO_BillHeader].patient_amount_base_2 as PATIENT_AMOUNT,  total_amount_base_2 as amount, [DESCRIPTION] , arabic_name, CASE_# as recordNo, DOCTOR_NAME"
  + " FROM [HMS909].[dbo].[SO_BillHeader]"
+ " join [HMS909].dbo.SO_BillDETAIL "
+ " on SO_BillDETAIL.BILL_# = [SO_BillHeader].BILL_# "
+ " where [CASE_#] =" + custid;
if(toDate != null && fromDate != null)
{
    query = query + " and [date] >= '" + fromDate + "' and [date] <='"+toDate+"'"; 
}

            
            var list = db.Database.SqlQuery<CustomerDetails>(query);



            return View(list);
        }

        public ActionResult Inactive( String fromDate)
        {

            if ( (string.IsNullOrEmpty(fromDate)))
            {


                DateTime startDate = DateTime.Now.AddMonths(-1);
                fromDate = Convert.ToString(startDate.Date);
            }


            ViewData["fromDate"] = fromDate;


            string query = "select [PATIENT] as full_name   ,[Arabic_Name] ,[CASE_#] as patID, convert(varchar,[last_visit],103)  as lastActive ,[MOBILE],[discounted]       ,[amount] as total from [hms909].[dbo].[Inactive_cust]  where [hms909].[dbo].[Inactive_cust].[last_visit] < '" + fromDate + "' order by discounted DESC";
            var list = db.Database.SqlQuery<PayingCustomer>(query);



            return View(list);
        }
        

    }
}
